package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.ChanPinXinXi;
import com.ysd.util.DBManager;

public class ChanPinXinXiErDaoImpl {
	public List<ChanPinXinXi> getChanPinXX(){
		List<ChanPinXinXi> cpList=new ArrayList<ChanPinXinXi>();
		Connection conn=DBManager.getConnection();
		Statement st=null;//提前声名变量
		ResultSet rs=null;//结果集
		
		try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM chanpinxinxi");
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				ChanPinXinXi cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, pp, dz, xqtpp, xqtp, ys, fl);
				cpList.add(cpxx);
			}
			return cpList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据产品id删除产品
	 * @param cPId
	 * @return
	 */
	public int delCPxx(int cPId){
		return DBManager.executeUpdate("DELETE FROM chanpinxinxi WHERE cPId="+cPId);
	}
	
	/**
	 * 多条件查询产品信息
	 * @param xx
	 * @return
	 */
	public List<ChanPinXinXi> getChanPinTJ(ChanPinXinXi x){
		List<ChanPinXinXi> xxList=new ArrayList<ChanPinXinXi>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			//1=1是一个永真条件（永远为true）
			String sql="SELECT cp.*,fl.cPName FROM chanpinxinxi cp,chanpinfenlei fl where 1=1";
			if(x.getcPId()!=0) {
				sql+=" and cPId LIKE '%"+x.getcPId()+"%'";
			}
			if(x.getcPName()!=null && !x.getcPName().equals("")) {
				sql+=" and cp.cPName LIKE '%"+x.getcPName()+"%'";
			}
			if(x.getBengincPShouJia()!=0) {
				sql+=" and cPShouJia>"+x.getBengincPShouJia();
			}
			if(x.getEndcPShouJia()!=0) {
				sql+=" and cPShouJia<"+x.getEndcPShouJia();
			}
			if(x.getPinPai()!=null && !x.getPinPai().equals("")) {
				sql+=" and pinPai LIKE '%"+x.getPinPai()+"%'";
			}
			if(x.getShiFouDZ()!=null && !x.getShiFouDZ().equals("")) {
				sql+=" and shiFouDZ LIKE '%"+x.getShiFouDZ()+"%'";
			}
			if(x.getChanPinYanSe()!=null && !x.getChanPinYanSe().equals("")) {
				sql+=" and chanPinYanSe LIKE '%"+x.getChanPinYanSe()+"%'";
			}
			if(x.getFenLeiID()!=0) {
				sql+=" and fenLeiID="+x.getFenLeiID();
			}
			sql+=" and cp.fenLeiID=fl.cPFenLeiId";
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				String cpnamey=rs.getString("fl.cPName");
				
				ChanPinXinXi cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, 0, 0, pp, dz, xqtpp, xqtp, ys, fl, cpnamey);
				xxList.add(cpxx);
			}
			return xxList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 多条件查询产品信息
	 * @param xx
	 * @return
	 */
	public List<ChanPinXinXi> getChanPinTJ(ChanPinXinXi x,int page,int size){
		List<ChanPinXinXi> xxList=new ArrayList<ChanPinXinXi>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			//1=1是一个永真条件（永远为true）
			String sql="SELECT cp.*,fl.cPName FROM chanpinxinxi cp,chanpinfenlei fl where 1=1";
			if(x.getcPId()!=0) {
				sql+=" and cPId LIKE '%"+x.getcPId()+"%'";
			}
			if(x.getcPName()!=null && !x.getcPName().equals("")) {
				sql+=" and cp.cPName LIKE '%"+x.getcPName()+"%'";
			}
			if(x.getBengincPShouJia()!=0) {
				sql+=" and cPShouJia>"+x.getBengincPShouJia();
			}
			if(x.getEndcPShouJia()!=0) {
				sql+=" and cPShouJia<"+x.getEndcPShouJia();
			}
			if(x.getPinPai()!=null && !x.getPinPai().equals("")) {
				sql+=" and pinPai LIKE '%"+x.getPinPai()+"%'";
			}
			if(x.getShiFouDZ()!=null && !x.getShiFouDZ().equals("")) {
				sql+=" and shiFouDZ LIKE '%"+x.getShiFouDZ()+"%'";
			}
			if(x.getChanPinYanSe()!=null && !x.getChanPinYanSe().equals("")) {
				sql+=" and chanPinYanSe LIKE '%"+x.getChanPinYanSe()+"%'";
			}
			if(x.getFenLeiID()!=0) {
				sql+=" and fenLeiID="+x.getFenLeiID();
			}
			sql+=" and cp.fenLeiID=fl.cPFenLeiId";
			if(x.getCpFenName()!=null && x.getCpFenName()!="") {
				sql+=" and fl.cPName='"+x.getCpFenName()+"'";
			}
			sql+=" limit "+(page-1)*2+","+size+"";
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				String cpnamey=rs.getString("fl.cPName");
				
				ChanPinXinXi cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, 0, 0, pp, dz, xqtpp, xqtp, ys, fl, cpnamey);
				xxList.add(cpxx);
			}
			return xxList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 多条件查询产品信息
	 * @param xx
	 * @return
	 */
	public List<ChanPinXinXi> getChanPinSize(ChanPinXinXi x){
		List<ChanPinXinXi> xxList=new ArrayList<ChanPinXinXi>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			//1=1是一个永真条件（永远为true）
			String sql="SELECT cp.*,fl.cPName FROM chanpinxinxi cp,chanpinfenlei fl where 1=1";
			if(x.getcPId()!=0) {
				sql+=" and cPId LIKE '%"+x.getcPId()+"%'";
			}
			if(x.getcPName()!=null && !x.getcPName().equals("")) {
				sql+=" and cp.cPName LIKE '%"+x.getcPName()+"%'";
			}
			if(x.getBengincPShouJia()!=0) {
				sql+=" and cPShouJia>"+x.getBengincPShouJia();
			}
			if(x.getEndcPShouJia()!=0) {
				sql+=" and cPShouJia<"+x.getEndcPShouJia();
			}
			if(x.getPinPai()!=null && !x.getPinPai().equals("")) {
				sql+=" and pinPai LIKE '%"+x.getPinPai()+"%'";
			}
			if(x.getShiFouDZ()!=null && !x.getShiFouDZ().equals("")) {
				sql+=" and shiFouDZ LIKE '%"+x.getShiFouDZ()+"%'";
			}
			if(x.getChanPinYanSe()!=null && !x.getChanPinYanSe().equals("")) {
				sql+=" and chanPinYanSe LIKE '%"+x.getChanPinYanSe()+"%'";
			}
			if(x.getFenLeiID()!=0) {
				sql+=" and fenLeiID="+x.getFenLeiID();
			}
			sql+=" and cp.fenLeiID=fl.cPFenLeiId";
			System.out.println(sql);
			rs=st.executeQuery(sql);
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				String cpnamey=rs.getString("fl.cPName");
				
				ChanPinXinXi cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, 0, 0, pp, dz, xqtpp, xqtp, ys, fl, cpnamey);
				xxList.add(cpxx);
			}
			return xxList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据产品id删除产品
	 * @param cPId
	 * @return
	 */
	public int delCPShanChu(int cPId){
		return DBManager.executeUpdate("DELETE FROM chanpinxinxi WHERE cPId="+cPId);
	}
	
	/**
	 * 批量删除
	 * @param cPId
	 * @return
	 */
	public int deletecPIds(String cPIds) {
		String sql = "DELETE FROM chanpinxinxi WHERE cPId in("+cPIds+")";
		System.out.println(sql) ;
		return DBManager.executeUpdate(sql);
	}
	/**
	 * 添加产品信息
	 * @param xx
	 * @return
	 */
	public int addCpXinXi(ChanPinXinXi xx) {
		return DBManager.executeUpdate("INSERT INTO chanpinxinxi VALUES(null,'"+xx.getTuPian()+"','"+xx.getcPName()+"',"+xx.getcPShouJia()+",'"+xx.getPinPai()+"','"+xx.getShiFouDZ()+"','"+xx.getxQTpp()+"','"+xx.getxQTp()+"','"+xx.getChanPinYanSe()+"','"+xx.getFenLeiID()+"')");
	}
	
	/**
	 * 根据名称查询产品信息
	 * @param name
	 * @return
	 */
	public List<ChanPinXinXi> getCpName(String name){
		List<ChanPinXinXi> xxList=new ArrayList<ChanPinXinXi>();
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * FROM chanpinxinxi WHERE cPName='"+name+"'");
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				ChanPinXinXi cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, pp, dz, xqtpp, xqtp, ys, fl);
				xxList.add(cpxx);
			}
			return xxList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 数据回显
	 * @param xxid
	 * @return
	 */
	public ChanPinXinXi getXinXiId(int xxid) {
		Connection conn=DBManager.getConnection();
		Statement st=null;
		ResultSet rs=null;
		ChanPinXinXi cpxx=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery("SELECT * from chanpinxinxi WHERE cPId="+xxid);
			while(rs.next()) {
				int cpId=rs.getInt("cPId");
				String tp=rs.getString("tuPian");
				String cpName=rs.getString("cPName");
				int shouJia=rs.getInt("cPShouJia");
				String pp=rs.getString("pinPai");
				String dz=rs.getString("shiFouDZ");
				String xqtpp=rs.getString("xQTpp");
				String xqtp=rs.getString("xQTp");
				String ys=rs.getString("chanPinYanSe");
				int fl=rs.getInt("fenLeiID");
				cpxx=new ChanPinXinXi(cpId, tp, cpName, shouJia, pp, dz, xqtpp, xqtp, ys, fl);
			}
			return cpxx;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	/**
	 * 修改产品信息
	 * @param d
	 * @return
	 */
	public int updateCPid(ChanPinXinXi d) {
		String sql="UPDATE chanpinxinxi SET tuPian='"+d.getTuPian()+"',cPName='"+d.getcPName()+"',cPShouJia='"+d.getcPShouJia()+"',pinPai='"+d.getPinPai()+"',shiFouDZ='"+d.getShiFouDZ()+"',xQTpp='"+d.getxQTpp()+"',xQTp='"+d.getxQTp()+"',chanPinYanSe='"+d.getChanPinYanSe()+"',fenLeiID='"+d.getFenLeiID()+"' WHERE cPId="+d.getcPId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
}
